package net.bolue.sysmng.db;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/**
 * sql处理类
 * @author Administrator
 *
 */
public class DbCreateSqlUtil {
	
	/**
	 * 生成updatesql语句
	 * @param table	表名	
	 * @param updataField	更新列
	 * @param whereField	条件列
	 * @return
	 */
	public static String createUpdateSql(String table,String[] updataField,String[] whereField){
		if(table==null||"".equals(table)||updataField==null||updataField.length<1){
			return null;
		}
		StringBuffer updateField = new StringBuffer();
		for(int i=0;i<updataField.length;i++){
			String field = updataField[i];
			if(updateField.length()!=0){
				updateField.append(" ,");
			}
			updateField.append(" "+field+"=? ");
		}
		
		StringBuffer _whereField = new StringBuffer();
		if(whereField!=null&&whereField.length>0){
			for(int i=0;i<whereField.length;i++){
				String field = whereField[i];
				if(_whereField.length()!=0){
					_whereField.append(" and ");
				}
				_whereField.append(" "+field+"=? ");
			}
		}
		
		StringBuffer _updateSql = new StringBuffer();
		_updateSql.append("update "+table+" set "+updateField+" ");
		if(_whereField.length()>0){
			_updateSql.append(" where "+_whereField);
		}
		String updateSql = _updateSql.toString();
		return updateSql;
	}

	/**
	 * 拼接更新sql
	 * @param table
	 * @param updateMap
	 * @param whereMap
	 * @return Map{String:sql,Object[]:paramters}
	 */
	public static Map<String,Object> createUpdateSql(String table,Map<String,Object> updateMap,Map<String,Object> whereMap){
		if(table==null||updateMap==null){
			return null;
		}
		
		Set<String> updateKeySet = updateMap.keySet();
		Set<String> whereKeySet = whereMap.keySet();
		Iterator<String> updateIt = updateKeySet.iterator();
		Integer updateNum = updateKeySet.size();
		Integer whereNum = whereKeySet.size();
		
		StringBuffer updateField = new StringBuffer();
		Object[] paramters = new Object[updateNum+whereNum];
		int index = 0;
		while(updateIt.hasNext()){
			String key = updateIt.next();
			Object value = updateMap.get(key);
			if(updateField.length()!=0){
				updateField.append(" ,");
			}
			updateField.append(" "+key+"=? ");
			paramters[index] = value;
			++index;
		}
		
		StringBuffer whereField = new StringBuffer();
		Iterator<String> whereIt = whereKeySet.iterator();
		while(whereIt.hasNext()){
			String key = whereIt.next();
			Object value = whereMap.get(key);
			if(whereField.length()!=0){
				whereField.append(" and ");
			}
			whereField.append(" "+key+"=? ");
			paramters[index] = value;
			++index;
		}
		
		StringBuffer _updateSql = new StringBuffer();
		_updateSql.append("update "+table+" set "+updateField+" ");
		if(whereField.length()>0){
			_updateSql.append(" where "+whereField);
		}
		String updateSql = _updateSql.toString();
		
		Map<String,Object> returnMap = new HashMap<String,Object>();
		returnMap.put("sql", updateSql);
		returnMap.put("paramters", paramters);
		return returnMap;
	}
	
	/**
	 * 生成插入语句
	 * @param table
	 * @param insField
	 * @return
	 */
	public static String createInsertSql(String table,String[] insField){
		if(table==null||"".equals(table)||insField==null||insField.length<1){
			return null;
		}
		StringBuffer insFieldBf = new StringBuffer();
		StringBuffer placeHolder = new StringBuffer();
		for(int i=0;i<insField.length;i++){
			String field = insField[i];
			if(insFieldBf.length()!=0){
				insFieldBf.append(" ,");
				placeHolder.append(" ,");
			}
			insFieldBf.append(" "+field+" ");
			placeHolder.append("?");
		}
		
		StringBuffer _insSql = new StringBuffer();
		_insSql.append("INSERT INTO "+table+"("+insFieldBf+") values("+placeHolder+") ");
		String insSql = _insSql.toString();
		return insSql;
	}
	
	/**
	 * 拼接插入sql
	 * @param table
	 * @param insertMap
	 * @return Map{String:sql,Object[]:paramters}
	 */
	public static Map<String,Object> createInsertSql(String table,Map<String,Object> insertMap){
		if(table==null|| insertMap==null){
			return null;
		}
		
		Set<String> insertKeySet = insertMap.keySet();
		Iterator<String> insertIt = insertKeySet.iterator();
		Integer insertNum = insertKeySet.size();
		
		StringBuffer insertField = new StringBuffer();
		StringBuffer placeHolder = new StringBuffer();
		Object[] paramters = new Object[insertNum];
		int index = 0;
		while(insertIt.hasNext()){
			String key = insertIt.next();
			Object value = insertMap.get(key);
			if(insertField.length()!=0){
				insertField.append(" ,");
				placeHolder.append(" ,");
			}
			insertField.append(" "+key+"");
			placeHolder.append("?");
			paramters[index] = value;
			++index;
		}
		
		StringBuffer _insertSql = new StringBuffer();
		_insertSql.append("insert into "+table+"("+insertField+") values("+placeHolder+")");
		String insertSql = _insertSql.toString();
		
		Map<String,Object> returnMap = new HashMap<String,Object>();
		returnMap.put("sql", insertSql);
		returnMap.put("paramters", paramters);
		return returnMap;
	}
	
	/**
	 * 拼接删除sql
	 * @param table
	 * @param whereMap
	 * @return
	 */
	public static Map<String,Object> createDeleteSql(String table,Map<String,Object> whereMap){
		if(table==null|| whereMap==null){
			return null;
		}
		
		Set<String> whereKeySet = whereMap.keySet();
		Iterator<String> whereIt = whereKeySet.iterator();
		Integer whereNum = whereKeySet.size();
		
		StringBuffer whereField = new StringBuffer();
		Object[] paramters = new Object[whereNum];
		int index = 0;
		while(whereIt.hasNext()){
			String key = whereIt.next();
			Object value = whereMap.get(key);
			if(whereField.length()!=0){
				whereField.append(" and");
			}
			whereField.append(" "+key+"=? ");
			paramters[index] = value;
			++index;
		}
		
		StringBuffer _deleteSql = new StringBuffer();
		_deleteSql.append("delete from "+table+" where "+whereField);
		String deleteSql = _deleteSql.toString();
		
		Map<String,Object> returnMap = new HashMap<String,Object>();
		returnMap.put("sql", deleteSql);
		returnMap.put("paramters", paramters);
		return returnMap;
	}
	
	/**
	 * 拼接查询sql
	 * @param table
	 * @param columns
	 * @param whereMap
	 * @return
	 */
	public static Map<String,Object> createSelectSql(String table,String[] columns,Map<String,Object> whereMap,Map<String,String> orderMap){
		if(table==null){
			return null;
		}
		
		StringBuffer column = new StringBuffer();
		if(columns!=null&&columns.length>0){
			for(int i=0;i<columns.length;i++){
				String columnName = columns[i];
				if(column.length()!=0){
					column.append(" ,");
				}
				column.append(" "+columnName+" ");
			}
		}else{
			column.append("*");
		}
		
		StringBuffer whereField = new StringBuffer();
		Object[] paramters = null;
		if(whereMap!=null&&whereMap.size()>0){
			String whereFlag = " where ";
			whereField.append(whereFlag);
			Set<String> whereKeySet = whereMap.keySet();
			Iterator<String> whereIt = whereKeySet.iterator();
			Integer whereNum = whereKeySet.size();
			
			paramters = new Object[whereNum];
			int index = 0;
			while(whereIt.hasNext()){
				String key = whereIt.next();
				Object value = whereMap.get(key);
				if(whereField.length()>whereFlag.length()){
					whereField.append(" and");
				}
				whereField.append(" "+key+"=? ");
				paramters[index] = value;
				++index;
			}
		}
		
		StringBuffer orderFieldsb = new StringBuffer();
		if(orderMap!=null){
			Set<String> orderSet = orderMap.keySet();
			Iterator<String> orderIt = orderSet.iterator();
			while(orderIt.hasNext()){
				String orderName = orderIt.next();
				String orderType = orderMap.get(orderName);
				if("".equals(orderType)||orderType==null){
					orderType = "DESC";
				}
				if(orderFieldsb.length()>0){
					orderFieldsb.append(",");
				}
				orderFieldsb.append(orderName+" "+orderType);
			}
		}
		String orderField = "";
		if(orderFieldsb.length()>0){
			orderField = " ORDER BY " + orderFieldsb.toString();
		}
		
		StringBuffer _selectSql = new StringBuffer();
		_selectSql.append("select "+column+" from "+table+whereField+orderField);
		String selectSql = _selectSql.toString();
		
		Map<String,Object> returnMap = new HashMap<String,Object>();
		returnMap.put("sql", selectSql);
		returnMap.put("paramters", paramters);
		return returnMap;
	}
	
}
